/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package admintool.imp03_data;

import admintool.AdminToolView;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 *
 * @author Administrator
 */
public class IMP03_Candidate {
    
    private class Candidate{

        private int candidateID = 0;
        private String lastName = "";
        private String firstName = "";
        private String plz = "";
        private String gender = "";
        private Date dateOfBirth = null;
        private String qualification = "";
        private int qualificationID = 0;
        
        public Candidate(int candidateID, String lastName, String firstName, String plz, String gender, Date dateOfBirth, String qualification, int qualificationID) {
            this.candidateID = candidateID;
            this.lastName = lastName;
            this.firstName = firstName;
            this.gender = gender;
            this.dateOfBirth = dateOfBirth;
            this.plz = plz;
            this.qualification = qualification;
            this.qualificationID = qualificationID;
        }

        public int getCandidateID() {
            return candidateID;
        }

        public Date getDateOfBirth() {
            return dateOfBirth;
        }

        public String getFirstName() {
            return firstName;
        }

        public String getGender() {
            return gender;
        }

        public String getLastName() {
            return lastName;
        }

        public String getPlz() {
            return plz;
        }

        public String getQualification() {
            return qualification;
        }

        public int getQualificationID() {
            return qualificationID;
        }
    }
    
    private static ArrayList<IMP03_Candidate> candidates = new ArrayList<IMP03_Candidate>();
    
    /**
     * 
     * @return
     */
    public static IMP03_Candidate getEmptyCandidateObject(){
        return new IMP03_Candidate(-1, 
                AdminToolView.EMPTY, 
                AdminToolView.EMPTY, 
                AdminToolView.EMPTY, 
                AdminToolView.EMPTY, 
                null, 
                AdminToolView.EMPTY,
                -1);
    }

    /**
     * 
     * @param candidateID
     * @param vacancyID
     * @return
     */
    public static int getMatchRatingForSpecificCandidateFromDB(int candidateID, int vacancyID){
        PreparedStatement ps = null;
        ResultSet rs = null;
        int matchRating = -1;

        try {
            candidates.clear();
            ps = AdminToolView.getDBConnection().prepareStatement("SELECT MatchRating FROM vacancy_candidate WHERE CandidateID = ? AND VacancyID = ?");
            ps.setInt(1, candidateID);
            ps.setInt(2, vacancyID);
            rs = ps.executeQuery();
            while(rs.next()){
                matchRating = rs.getInt(1);
                break;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if(rs != null){
                    rs.close();
                }
                if(ps != null){
                    ps.close();
                }
            } catch (Exception e) {
            }
        }
        return matchRating;
    }
    
    /**
     * 
     * @param candidateID
     * @return
     */
    public static IMP03_Candidate getSpecificCandidateFromDB(int candidateID){
        PreparedStatement ps = null;
        ResultSet rs = null;
        IMP03_Candidate result = null;

        try {
            candidates.clear();
            ps = AdminToolView.getDBConnection().prepareStatement("SELECT C.CandidateID, C.LastName, C.FirstName, C.PLZ, G.Gender, C.DateOfBirth, Q.Name, Q.QualificationID FROM (candidate C LEFT JOIN gender G ON C.GenderID = G.GenderID) LEFT JOIN qualification Q ON C.QualificationID = Q.QualificationID WHERE CandidateID = ?");
            ps.setInt(1, candidateID);
            rs = ps.executeQuery();
            while(rs.next()){
                result = new IMP03_Candidate(
                        rs.getInt(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4),
                        rs.getString(5),
                        rs.getDate(6),
                        rs.getString(7),
                        rs.getInt(8));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if(rs != null){
                    rs.close();
                }
                if(ps != null){
                    ps.close();
                }
            } catch (Exception e) {
            }
        }
        return result;
    }
    
    /**
     * 
     * @param reload
     * @return
     */
    public static ArrayList<IMP03_Candidate> getAllAvailableCandidatesForSpecificPlatform(IMP03_Platform platform){
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList<IMP03_Candidate> platformCandidate = new ArrayList<IMP03_Candidate>();

        try {
            platformCandidate.clear();
            ps = AdminToolView.getDBConnection().prepareStatement("SELECT C.CandidateID, C.LastName, C.FirstName, C.PLZ, G.Gender, C.DateOfBirth, Q.Name, C.QualificationID FROM ((candidate C LEFT JOIN gender G ON C.GenderID = G.GenderID) LEFT JOIN qualification Q ON C.QualificationID = Q.QualificationID) JOIN platform_candidates PC ON C.CandidateID = PC.CandidateID WHERE PC.PlatformID = ?");
            ps.setInt(1, platform.getPlatformID());
            rs = ps.executeQuery();
            while(rs.next()){
                platformCandidate.add(new IMP03_Candidate(
                        rs.getInt(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4),
                        rs.getString(5),
                        rs.getDate(6),
                        rs.getString(7),
                        rs.getInt(8)));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if(rs != null){
                    rs.close();
                }
                if(ps != null){
                    ps.close();
                }
            } catch (Exception e) {
            }
        }
        return platformCandidate;
    }    
    
    
    /**
     * 
     * @param reload
     * @return
     */
    public static ArrayList<IMP03_Candidate> getAllAvailableCandidatesFromDB(boolean reload){
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            if(reload == true || candidates.isEmpty()){
                candidates.clear();
                ps = AdminToolView.getDBConnection().prepareStatement("SELECT C.CandidateID, C.LastName, C.FirstName, C.PLZ, G.Gender, C.DateOfBirth, Q.Name, C.QualificationID FROM (candidate C LEFT JOIN gender G ON C.GenderID = G.GenderID) LEFT JOIN qualification Q ON C.QualificationID = Q.QualificationID");
                rs = ps.executeQuery();
                while(rs.next()){
                    candidates.add(new IMP03_Candidate(
                            rs.getInt(1),
                            rs.getString(2),
                            rs.getString(3),
                            rs.getString(4),
                            rs.getString(5),
                            rs.getDate(6),
                            rs.getString(7),
                            rs.getInt(8)));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if(rs != null){
                    rs.close();
                }
                if(ps != null){
                    ps.close();
                }
            } catch (Exception e) {
            }
        }
        return candidates;
    }
    
    private Candidate candidate = null;
    private ArrayList<IMP03_Skill> candidateSkills = new ArrayList<IMP03_Skill>();
    private ArrayList<IMP03_Platform> candidatePlatforms =  new ArrayList<IMP03_Platform>();
    private ArrayList<IMP03_Experience> candidateExperience = new ArrayList<IMP03_Experience>();
    
    /**
     * 
     * @param candidateID
     * @param lastName
     * @param firstName
     * @param plz
     * @param gender
     * @param dateOfBirth
     * @param qualification
     * @param qualificationID
     */
    public IMP03_Candidate(int candidateID, String lastName, String firstName, String plz, String gender, Date dateOfBirth, String qualification, int qualificationID) {
        this.candidate = new Candidate(candidateID, lastName, firstName, plz, gender, dateOfBirth, qualification, qualificationID);
    }

    @Override
    public String toString() {
        return candidate.getLastName() + ", " + candidate.getFirstName();
    }
    
    /**
     * 
     * @return
     */
    public int getCandidateID(){
        return candidate.getCandidateID();
    }

    /**
     * 
     * @return
     */
    public String getLastName(){
        return candidate.getLastName();
    }

    /**
     * 
     * @return
     */
    public String getFirstName(){
        return candidate.getFirstName();
    }

    /**
     * 
     * @return
     */
    public String getGender(){
        return candidate.getGender();
    }

    /**
     * 
     * @return
     */
    public Date getDateOfBirth(){
        return candidate.getDateOfBirth();
    }

    /**
     * 
     * @return
     */
    public String getPLZ(){
        return candidate.getPlz();
    }

    /**
     * 
     * @return
     */
    public String getQualification(){
        return candidate.getQualification();
    }
    
    /**
     * 
     * @return
     */
    public ArrayList<IMP03_Skill> getCandidateSkills(){
        initCandidateSkills();
        return candidateSkills;    
    }    

    /**
     * 
     * @return
     */
    public int getQualificationID(){
        return candidate.getQualificationID();
    }

    /**
     * 
     * @return
     */
    public ArrayList<IMP03_Platform> getCandidatePlatforms(){
        initCandidatePlatforms();
        return candidatePlatforms;    
    }    
    
    /**
     * 
     * @return
     */
    public ArrayList<IMP03_Experience> getCandidateExperience(){
        initCandidateExperience();
        return candidateExperience;    
    }    
    
    private void initCandidateSkills() {
        PreparedStatement ps = null;
        ResultSet rs = null;
        candidateSkills.clear();
        
        try {
            ps = AdminToolView.getDBConnection().prepareStatement("SELECT SkillID FROM candidate_skills WHERE candidateID = ?");
            ps.setInt(1, getCandidateID());
            rs = ps.executeQuery();
            ArrayList<Integer> skillIDs = new ArrayList<Integer>();
            while(rs.next()){
                skillIDs.add(rs.getInt(1));
            }
            
            for(IMP03_Skill s : IMP03_Skill.getAllAvailableSkillsFromDB(false)){
                if(skillIDs.contains(s.getSkillID())){
                    candidateSkills.add(s);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            try {
                if(rs != null){
                    rs.close();
                }
                if(ps != null){
                    ps.close();
                }
            } catch (Exception e) {
            }
        }
    }     
    
    /**
     * 
     * @param skillID
     * @return
     */
    public int getSkillLevelForCandidate(int skillID){
        PreparedStatement ps = null;
        ResultSet rs = null;
        int skillLevel = -1;
        
        try {
            ps = AdminToolView.getDBConnection().prepareStatement("SELECT SkillLevel FROM candidate_skills WHERE SkillID = ? AND CandidateID = ?");
            ps.setInt(1, skillID);
            ps.setInt(2, getCandidateID());
            rs = ps.executeQuery();
            while(rs.next()){
                skillLevel = rs.getInt(1);
                break;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            try {
                if(rs != null){
                    rs.close();
                }
                if(ps != null){
                    ps.close();
                }
            } catch (Exception e) {
            }
        }
        return skillLevel;
    }    
    
    
    private void initCandidateExperience() {
        PreparedStatement ps = null;
        ResultSet rs = null;
        candidateExperience.clear();
        
        try {
            ps = AdminToolView.getDBConnection().prepareStatement("SELECT ExperienceID FROM experience WHERE candidateID = ?");
            ps.setInt(1, getCandidateID());
            rs = ps.executeQuery();
            ArrayList<Integer> experienceIDs = new ArrayList<Integer>();
            while(rs.next()){
                experienceIDs.add(rs.getInt(1));
            }
            
            for(IMP03_Experience e : IMP03_Experience.getAllAvailableExperienceFromDB(true, getCandidateID())){
                if(experienceIDs.contains(e.getExperienceID())){
                    candidateExperience.add(e);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            try {
                if(rs != null){
                    rs.close();
                }
                if(ps != null){
                    ps.close();
                }
            } catch (Exception e) {
            }
        }
    }            
    
    private void initCandidatePlatforms() {
        PreparedStatement ps = null;
        ResultSet rs = null;
        candidatePlatforms.clear();
        
        try {
            ps = AdminToolView.getDBConnection().prepareStatement("SELECT PlatformID FROM platform_candidates WHERE candidateID = ?");
            ps.setInt(1, getCandidateID());
            rs = ps.executeQuery();
            ArrayList<Integer> platformIDs = new ArrayList<Integer>();
            while(rs.next()){
                platformIDs.add(rs.getInt(1));
            }
            
            for(IMP03_Platform p : IMP03_Platform.getAllAvailablePlatformsFromDB(false)){
                if(platformIDs.contains(p.getPlatformID())){
                    candidatePlatforms.add(p);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            try {
                if(rs != null){
                    rs.close();
                }
                if(ps != null){
                    ps.close();
                }
            } catch (Exception e) {
            }
        }
    }        
}
